from .common_func.print_logger import PrintLogger as Logger
from peewee import Database, ImproperlyConfigured, ViewMetadata, ColumnMetadata, \
    ForeignKeyMetadata, fn, NodeList, SQL, IndexMetadata
from playhouse.pool import PooledDatabase

import_error_warned = False

# Rules for parentheses around sub queries in compound select.
CSQ_PARENTHESES_NEVER = 0
CSQ_PARENTHESES_ALWAYS = 1
CSQ_PARENTHESES_UNNESTED = 2


class DM8Database(Database):
    # 与 peewee默认定义不一致的值，根据dm里的数据类型进行调整
    field_types = {
        'AUTO': 'INTEGER',
        'BOOL': 'BOOLEAN',
        'DATETIME': 'TIMESTAMP'
    }

    # 与 peewee默认定义不一致的值， 根据dm里的SQL expressions 进行调整
    operations = {'NE': '<>',
                  'ILIKE': 'LIKE'}
    # 替换为dm中的通配符 ，是?号，保持默认即可
    # param = '%s'

    # 嵌套子查询是否要携带括号
    compound_select_parentheses = CSQ_PARENTHESES_ALWAYS
    # 支持 select ... for update 语句
    for_update = True
    # mysql风格的 需要设置为True，这里不确定要不要设置，先使用默认
    # index_using_precedes_table = False
    # 是否支持returning 语法: dm不支持单条sql中使用returnning 语句,存储过程中可以
    returning_clause = False
    # 是否支持序列
    sequences = True

    def init(self, database=None, local_code=None, **kwargs):
        try:
            import dmPython
        except ModuleNotFoundError as e:
            global import_error_warned
            if not import_error_warned:
                Logger.warning("Class DM8Database is not valid, because require module:dmPython is not installed. "
                               "If you want use DM8Database, please install dmPython first")
                import_error_warned = True
                raise ModuleNotFoundError(e)
        self._local_code = local_code
        self._user = kwargs['user']
        super(DM8Database, self).init(database, **kwargs)

    def _connect(self):
        try:
            import dmPython
        except ModuleNotFoundError:
            raise ImproperlyConfigured('dm8 driver not installed!')

        params = self.connect_params.copy()
        if self._local_code:  # 设置本地编码，默认当前环境系统编码
            params['local_code'] = self._local_code
        conn = dmPython.connect(**params)
        conn.autoCommit = True
        return conn

    def set_atuo_commit(self, target):
        conn = self.connection()
        conn.autoCommit = target

    # !!!!!! dmPython不支持形如 on conflict do ..的操作,使用dm的merge into 原生sql语句！！
    def conflict_statement(self, on_conflict, query):
        raise ValueError('Un-supported action for conflict resolution for dm8!')

    # !!!!!! dmPython不支持形如 on conflict do ..的操作,使用dm的merge into 原生sql语句！！
    def conflict_update(self, on_conflict, query):
        raise ValueError('Un-supported action for conflict resolution for dm8!')

    def get_tables(self, schema=None):
        """
        获取库中的所有表的表名
        """
        query = ('SELECT TABLE_NAME FROM USER_TABLES '
                 'ORDER BY TABLE_NAME')
        cursor = self.execute_sql(query, schema)
        return [table for table, in cursor.fetchall()]

    def get_views(self, schema=None):
        """
        获取库中的视图名及构成的sql
        """
        query = 'SELECT VIEW_NAME, TEXT FROM USER_VIEWS'
        cursor = self.execute_sql(query)
        return [ViewMetadata(*row) for row in cursor.fetchall()]

    def get_indexes(self, table, schema=None):
        """
        根据表名获取索引信息
        """
        query = """
                SELECT /*+GROUP_OPT_FLAG(1)*/
                a.INDEX_NAME,a.UNIQUENESS,LISTAGG(b.COLUMN_NAME,',') WITHIN GROUP(ORDER BY b.COLUMN_NAME) AS columns
                FROM USER_INDEXES a
                JOIN USER_IND_COLUMNS b on a.INDEX_NAME=b.INDEX_NAME
                WHERE a.TABLE_NAME = ?
                GROUP BY a.INDEX_NAME
                """
        cursor = self.execute_sql(query, table)
        return [IndexMetadata(name, None, columns.split(','),
                              unique.lower() == 'unique', table)
                for name, unique, columns in cursor.fetchall()]

    def get_columns(self, table, schema=None):
        """
        根据表名获取字段信息
        """
        query = """
                SELECT COLUMN_NAME, NULLABLE, DATA_TYPE, DATA_DEFAULT
                FROM ALL_TAB_COLUMNS
                WHERE TABLE_NAME = ?
                AND A.OWNER= ?
            """
        cursor = self.execute_sql(query, (table, self._user))
        pks = set(self.get_primary_keys(table, schema))
        return [ColumnMetadata(name, dt, null == 'Y', name in pks, table, df)
                for name, null, dt, df in cursor.fetchall()]

    def get_primary_keys(self, table, schema=None):
        """
        根据表名获取主键
        """
        query = """
                    SELECT B.COLUMN_NAME
                    FROM ALL_CONSTRAINTS A,
                         ALL_CONS_COLUMNS B
                    WHERE B.CONSTRAINT_NAME = A.CONSTRAINT_NAME
                    AND B.TABLE_NAME = ?
                    AND A.CONSTRAINT_TYPE = 'P'
                    AND A.OWNER= ?
                   """
        cursor = self.execute_sql(query, (table, self._user))
        return [pk for pk, in cursor.fetchall()]

    def get_foreign_keys(self, table, schema=None):
        """
        根据表名获取外键
        """
        sql = """
                SELECT B.COLUMN_NAME
                FROM ALL_CONSTRAINTS A,
                     ALL_CONS_COLUMNS B
                WHERE B.CONSTRAINT_NAME = A.CONSTRAINT_NAME
                AND B.TABLE_NAME = ?
                AND A.CONSTRAINT_TYPE = 'R'
                AND A.OWNER= ?
               """
        cursor = self.execute_sql(sql, (table, self._user))
        return [ForeignKeyMetadata(row[0], row[1], row[2], table)
                for row in cursor.fetchall()]

    def sequence_exists(self, sequence):
        """
        库中是否存在序列，返回True/False
        """
        res = self.execute_sql(f"""
                    SELECT COUNT(1) FROM USER_SEQUENCES 
                    WHERE SEQUENCE_NAME='{sequence}'  """)
        return bool(res.fetchone()[0])

    def extract_date(self, date_part, date_field):
        # 从日期时间类型或时间间隔类型的参数 date 中抽取 dtfield 对应的数值，并返回一个数字值
        return fn.EXTRACT(NodeList((date_part, SQL('FROM'), date_field)))

    def truncate_date(self, date_part, date_field):
        # 将日期时间 date 截断到最接近格式参数 fmt 指定的形式
        return fn.TRUNC(date_part, date_field)

    def to_timestamp(self, date_field):
        # 自标准时区的'1970-01-01 00:00:00 +0:00'到本地会话时区的指定时间的秒数差
        return fn.UNIX_TIMESTAMP(date_field)

    def from_timestamp(self, date_field):
        # 将自'1970-01-01 00:00:00'的秒数差转成本地会话时区的时间戳类型。
        return fn.FROM_UNIXTIME(date_field)

    def begin(self):
        """
        事务的起始语句，不同于PG以BEGIN开始，dm没有BEGIN,直接commit即可
        """
        # just check connection here
        if self.is_closed():
            self.connect()


# dm8适配playhouse的数据库连接池
class PooledDM8Database(PooledDatabase, DM8Database):
    def _is_closed(self, conn):
        try:
            conn.ping()
        except:
            return True
        else:
            return False
